﻿-- =============================================
-- System Name:    Pre-Contract
-- Component:      Changes for vw_PreContractInfo
-- Created By:     Leo Ren (leoren@wicresoft.com)
-- Created Date:   2011-6-22
-- =============================================
/****** Object:  View [dbo].[vw_PreContractInfo]    Script Date: 06/22/2011 16:26:31 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_PreContractInfo]'))
DROP VIEW [dbo].[vw_PreContractInfo]
GO
/****** Object:  View [dbo].[vw_PreContractInfo]    Script Date: 06/22/2011 16:26:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_PreContractInfo]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vw_PreContractInfo]
AS
SELECT     TOP (100) PERCENT dbo.PreContractInfo.PreContractID, dbo.PreContractInfo.PreContractNo, dbo.PreContractInfo.StatusID, dbo.PreContractInfo.TypeID, 
                      dbo.PreContractInfo.ProductID, dbo.PreContractInfo.PromisedDate, dbo.PreContractInfo.CEWPromised, dbo.PreContractInfo.OrionPrice, dbo.PreContractInfo.SalePrice, 
                      dbo.PreContractInfo.SignDue, dbo.PreContractInfo.SignActual, dbo.PreContractInfo.ContractName, dbo.PreContractInfo.ERPReceived, dbo.PreContractInfo.OrionNo, 
                      dbo.PreContractInfo.Delivered, dbo.PreContractInfo.CurrentSituation, dbo.PreContractInfo.BlackDate, dbo.PreContractInfo.TradeMonth AS TradeMonth_Date, 
                      MONTH(dbo.PreContractInfo.TradeMonth) AS TradeMonth, YEAR(dbo.PreContractInfo.TradeMonth) AS TradeYear, DATEPART(q, dbo.PreContractInfo.TradeMonth) 
                      AS TradeQuater, dbo.PreContractInfo.ModifiedDate, dbo.PreContractInfo.ACRNo, dbo.PreContractInfo.IsRisk, 
                      CASE dbo.PreContractInfo.ASBorLucent WHEN 0 THEN ''ASB'' WHEN 1 THEN ''Lucent'' END AS ASBorLucentName, dbo.PreContractInfo.Applicant, 
                      dbo.PreContractInfo.ApplicantEmail, dbo.PreContractInfo.Principal, dbo.PreContractInfo.PrincipalEmail, dbo.PreContractInfo.ProvinceID, dbo.PreContractInfo.CATID, 
                      dbo.PreContractInfo.BDID, dbo.PreContractInfo.CustomerName, dbo.PreContractInfo.CustomField01, dbo.PreContractInfo.CustomField02, 
                      dbo.PreContractInfo.CustomField03, dbo.PreContractInfo.CustomField04, dbo.PreContractInfo.DeliveryPercent, dbo.CAT.CATCode, dbo.BG.BGCode, dbo.BD.BDCode, 
                      dbo.Province.ProvinceCode, dbo.DicItem.DicItemValue AS PreContractStatus, DicItem_1.DicItemValue AS PreContractType, dbo.Product.ProductName, 
                      dbo.PreContractInfo.ASBorLucent, dbo.ExchangeRate.Rate, ROUND(dbo.PreContractInfo.SparePrice / dbo.ExchangeRate.Rate / 1170000, 4) AS CEWME, 
                      ROUND(dbo.PreContractInfo.OrionPrice / dbo.ExchangeRate.Rate / 1170000, 4) AS OrionME, 
                      ROUND(dbo.PreContractInfo.OrionPrice / dbo.ExchangeRate.Rate / 1170000, 4) - ROUND(dbo.PreContractInfo.SparePrice / dbo.ExchangeRate.Rate / 1170000, 4) 
                      AS GAP, dbo.vw_PreContractInfoComment_Newest.Comment, dbo.PreContractInfo.AdvancedContract, dbo.vw_PreContractInfoComment_Newest.DateCreated, 
                      dbo.Province.ProvinceEName, dbo.Province.ProvinceCName, dbo.vw_PreContractInfoComment_Newest.Creator, 
                      dbo.vw_PreContractInfoComment_Newest.CommentPromisedDate, dbo.vw_PreContractInfoComment_Newest.Comment AS Comments, dbo.BG.BGID, 
                      CASE precontractinfo.IsRisk WHEN 0 THEN ''Secure'' WHEN 1 THEN ''Risk'' END AS IsRiskDesc, dbo.BD.Active AS BDActive, dbo.Province.Active AS ProvinceActive, 
                      dbo.CAT.Active AS CATActive, dbo.PreContractInfo.DeliveryDate, dbo.PreContractInfo.Flag, DATEDIFF(DAY, dbo.PreContractInfo.PromisedDate, GETDATE()) 
                      / 7 AS DelayWeeks, dbo.PreContractInfo.IsDelay, CASE WHEN dbo.PreContractInfo.StatusID = 12 THEN DateDiff(day, ERPReceived, SignActual) ELSE DateDiff(day, 
                      ERPReceived, getdate()) END / 31 AS ExistMonth, dbo.PreContractInfo.AdjustID, dbo.PreContractInfo.SparePrice, dbo.PreContractInfo.CFIPrice, 
                      dbo.PreContractInfo.DeliveryPlan, dbo.Province.ClusterID, DicItem_2.DicItemValue AS PreContractAdjust, dbo.ProvinceCluster.ClusterCName, 
                      dbo.ProvinceCluster.ClusterEName, dbo.PreContractInfo.UpdateReason, dbo.PreContractInfo.AOGDate, dbo.PreContractInfo.TransferHistoryInfo, 
                      dbo.PreContractInfo.SpecialApprove
FROM         dbo.CAT RIGHT OUTER JOIN
                      dbo.PreContractInfo LEFT OUTER JOIN
                      dbo.vw_PreContractInfoComment_Newest ON dbo.PreContractInfo.PreContractID = dbo.vw_PreContractInfoComment_Newest.PreContractID ON 
                      dbo.CAT.CATID = dbo.PreContractInfo.CATID LEFT OUTER JOIN
                      dbo.Province ON dbo.PreContractInfo.ProvinceID = dbo.Province.ProvinceID LEFT OUTER JOIN
                      dbo.DicItem ON dbo.PreContractInfo.StatusID = dbo.DicItem.DicItemID LEFT OUTER JOIN
                      dbo.DicItem AS DicItem_1 ON dbo.PreContractInfo.TypeID = DicItem_1.DicItemID LEFT OUTER JOIN
                      dbo.Product ON dbo.PreContractInfo.ProductID = dbo.Product.ProductID LEFT OUTER JOIN
                      dbo.BG RIGHT OUTER JOIN
                      dbo.BD ON dbo.BG.BGID = dbo.BD.BGID ON dbo.PreContractInfo.BDID = dbo.BD.BDID CROSS JOIN
                      dbo.ExchangeRate LEFT OUTER JOIN
                      dbo.DicItem AS DicItem_2 ON dbo.PreContractInfo.AdjustID = DicItem_2.DicItemID LEFT OUTER JOIN
                      dbo.ProvinceCluster ON dbo.Province.ClusterID = dbo.ProvinceCluster.ProvinceClusterID
ORDER BY dbo.PreContractInfo.StatusID, dbo.PreContractInfo.PreContractNo
'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPane1' , N'SCHEMA',N'dbo', N'VIEW',N'vw_PreContractInfo', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[18] 4[4] 3[4] 2) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "CAT"
            Begin Extent = 
               Top = 224
               Left = 584
               Bottom = 339
               Right = 768
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "PreContractInfo"
            Begin Extent = 
               Top = 53
               Left = 37
               Bottom = 168
               Right = 197
            End
            DisplayFlags = 280
            TopColumn = 43
         End
         Begin Table = "vw_PreContractInfoComment_Newest"
            Begin Extent = 
               Top = 121
               Left = 251
               Bottom = 236
               Right = 466
            End
            DisplayFlags = 280
            TopColumn = 5
         End
         Begin Table = "Province"
            Begin Extent = 
               Top = 139
               Left = 542
               Bottom = 254
               Right = 698
            End
            DisplayFlags = 280
            TopColumn = 1
         End
         Begin Table = "DicItem"
            Begin Extent = 
               Top = 73
               Left = 239
               Bottom = 188
               Right = 391
            End
            DisplayFlags = 280
            TopColumn = 1
         End
         Begin Table = "DicItem_1"
            Begin Extent = 
               Top = 117
               Left = 358
               Bottom = 232
               Right = 510
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "Product"
            Begin Extent = 
               Top = 9
               Left = 22
               Bottom = 124
               Right = 174
            End' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_PreContractInfo'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPane2' , N'SCHEMA',N'dbo', N'VIEW',N'vw_PreContractInfo', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "BG"
            Begin Extent = 
               Top = 5
               Left = 615
               Bottom = 120
               Right = 767
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "BD"
            Begin Extent = 
               Top = 18
               Left = 427
               Bottom = 133
               Right = 579
            End
            DisplayFlags = 280
            TopColumn = 4
         End
         Begin Table = "ExchangeRate"
            Begin Extent = 
               Top = 172
               Left = 806
               Bottom = 272
               Right = 968
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "DicItem_2"
            Begin Extent = 
               Top = 168
               Left = 38
               Bottom = 276
               Right = 180
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "ProvinceCluster"
            Begin Extent = 
               Top = 234
               Left = 218
               Bottom = 342
               Right = 384
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 59
         Width = 284
         Width = 1500
         Width = 1500
         Width = 2400
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 2505
         Alias = 1605
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_PreContractInfo'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPaneCount' , N'SCHEMA',N'dbo', N'VIEW',N'vw_PreContractInfo', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vw_PreContractInfo'
GO
